package com.yao.util;

import com.yao.util.model.ClassTableRealation;

import java.sql.*;

/**
 * Created with IntelliJ IDEA.
 * User: tao95
 * Date: 2017/5/5
 * Time: 10:40
 * To change this template use File | Settings | File Templates.
 * Description:
 */
public class DBHelper {
    public static final String url = "jdbc:mysql://120.27.93.175:3306/" +
            "class_table?useUnicode=true&characterEncoding=UTF-8";
    public static final String name = "com.mysql.jdbc.Driver";
    public static final String user = "root";
    public static final String password = "jintao123";

    public Connection conn = null;
    public PreparedStatement pst = null;

    public DBHelper(String sql) {
        try {
            Class.forName(name);//指定连接类型
            conn = DriverManager.getConnection(url, user, password);//获取连接
            pst = conn.prepareStatement(sql);//准备执行语句
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static ClassTableRealation getCourseTable(int dayOfWeek,int classOfDay,int laboratory_id,int sort){
        String sql = "select * from class_table_relation WHERE days_id =" + dayOfWeek+
                " and course_time = "+classOfDay+" AND laboratory_id = "+laboratory_id;
        DBHelper db1 = new DBHelper(sql);//创建DBHelper对象
        ResultSet ret = null;
        ClassTableRealation ctr = new ClassTableRealation();
        String className = null;
        String courseName = null;
        try {
            ret = db1.pst.executeQuery();//执行语句，得到结果集
            while (ret.next()) {
                className = ret.getString("class_name");
                courseName = ret.getString("course_name");
                System.out.println(className + "\t" + courseName  );
            }//显示数据
            ret.close();
            db1.close();//关闭连接
        } catch (SQLException e) {
            e.printStackTrace();
        }
        ctr.setClassOfday(sort);
        ctr.setClassName(className);
        ctr.setCourseName(courseName);
        return ctr;
    }

    public void close() {
        try {
            this.conn.close();
            this.pst.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}